<?php

class m_pinjaman extends CI_Model {

    var $local_client;
    
    function __construct() {
        parent::__construct();
        $this->local_client = $this->load->database('default', TRUE);
    }

    public function get($where = null, $limit = null, $offset = null) {
        $this->local_client->select("anggota.ID_ANGGOTA");
        $this->local_client->select("anggota.NAMA_ANGGOTA");
        $this->local_client->select("pinjaman.*");
        $this->local_client->select("jenis_kredit.ID_JENIS_KREDIT");
        $this->local_client->select("jenis_kredit.NAMA_KREDIT");
        $this->local_client->select("info_usaha.KODE_USAHA");
        $this->local_client->select("info_usaha.URAIAN_USAHA");

        $this->local_client->join("anggota", "anggota.ID_ANGGOTA = pinjaman.ID_ANGGOTA");
        $this->local_client->join("jenis_kredit", "jenis_kredit.ID_JENIS_KREDIT = pinjaman.ID_JENIS_KREDIT");
        $this->local_client->join("info_usaha", "info_usaha.KODE_USAHA = pinjaman.ID_INFO_USAHA");

        if($where != null) $this->local_client->where($where);
        if($limit != null && $offset != null) $this->local_client->limit($limit, $offset);
        return $this->local_client->get("pinjaman");
    }

    function get_all($limit, $uri) {
        $result = $this->local_client->get('pinjaman', $limit, $uri);
        if ($result->num_rows() > 0) {
            return $result->result_array();
        } else {
            return array();
        }
    }

    function get_one($id) {

        $this->local_client = $this->load->database('default', TRUE);

        $this->local_client->select("anggota.*");
        $this->local_client->select("pinjaman.*");
        $this->local_client->select("jenis_kredit.*");
        $this->local_client->select("info_usaha.*");

        $this->local_client->join("anggota", "anggota.ID_ANGGOTA = pinjaman.ID_ANGGOTA");
        $this->local_client->join("jenis_kredit", "jenis_kredit.ID_JENIS_KREDIT = pinjaman.ID_JENIS_KREDIT");
        $this->local_client->join("info_usaha", "info_usaha.KODE_USAHA = pinjaman.ID_INFO_USAHA");

        $this->local_client->where('ID_PINJAMAN', $id);

        return $this->local_client->get('pinjaman');
        
    }

    function reject($id) {
        $data = array(
                'STATUS' => -1
        );

        $this->local_client->where('ID_PINJAMAN', $id);
        return $this->local_client->update('pinjaman', $data);
    }

    function approve($id) {
        $data = array(
                'STATUS' => 1,
                'ID_BUKTI_TRANSAKSI' => $this->input->post('BKK_BKB', TRUE)
        );

        $this->local_client->where('ID_PINJAMAN', $id);
        return $this->local_client->update('pinjaman', $data);
    }

    function approve2($id,$id_bukti_transaksi) {
        $data = array(
                'STATUS' => 1,
                'ID_BUKTI_TRANSAKSI' => $id_bukti_transaksi
        );

        $this->local_client->where('ID_PINJAMAN', $id);
        return $this->local_client->update('pinjaman', $data);
    }

    function insert_data($id_anggota, $id_jenis_kredit, $id_info_usaha,
            $tgl_pinjaman, $lama_pinjaman, $jumlah_pinjaman, $cicilan_pokok, $bunga)
    {
        $data = array(
                'ID_ANGGOTA' => $id_anggota,
                'ID_JENIS_KREDIT' => $id_jenis_kredit,
                'ID_INFO_USAHA' => $id_info_usaha,
                'TGL_PINJAMAN' => $tgl_pinjaman,
                'LAMA_PINJAMAN' => $lama_pinjaman,
                'JUMLAH_PINJAMAN' => $jumlah_pinjaman,
                'CICILAN_POKOK' => $cicilan_pokok,
                'BUNGA' => $bunga
        );

        $this->local_client = $this->load->database('default', TRUE);

        return $this->local_client->insert('pinjaman', $data);
    }

    function insert() {
        $info_usaha = $this->input->post('JENIS_PELAYANAN', TRUE);

        $jumlah = $this->input->post('MAX_PIUTANG', TRUE);
        $lama = $this->input->post('MAX_CICIL', TRUE);
        $bunga = $this->input->post('BUNGA', TRUE);
        $keterangan = $this->input->post('KETERANGAN', TRUE);

        $jenis_kredit = $this->input->post('TIPE_KREDIT', TRUE);

        $margin = $this->input->post('MARGIN', TRUE) / $lama;
        if($margin == "")
            $margin = $bunga * $jumlah / 100;

        if ($info_usaha == 0)
        {
            $info_usaha = $this->input->post('KODE_USAHA', TRUE);
            $jenis_kredit = 2;
            $margin = $this->input->post('MARGIN', TRUE);
        }

        $id_anggota = $this->input->post('ID_ANGGOTA', TRUE);
        if($id_anggota == "")
            $id_anggota = $this->session->userdata("ID_ANGGOTA");

        $data = array(
                'ID_ANGGOTA' => $id_anggota,
                'ID_JENIS_KREDIT' => $jenis_kredit,
                'ID_INFO_USAHA' => $info_usaha,
                'TGL_PINJAMAN' => $this->input->post('TANGGAL_PINJAM', TRUE),
                'LAMA_PINJAMAN' => $lama,
                'JUMLAH_PINJAMAN' => $jumlah,
                'MARGIN' => $margin,
                'CICILAN_POKOK' => $jumlah / $lama,
                'BUNGA' => $bunga,
                'KETERANGAN' => $keterangan
        );

        return $this->local_client->insert('pinjaman', $data);
    }

    function update() {
        $info_usaha = $this->input->post('JENIS_PELAYANAN', TRUE);

        $jumlah = $this->input->post('MAX_PIUTANG', TRUE);
        $lama = $this->input->post('MAX_CICIL', TRUE);
        $bunga = $this->input->post('BUNGA', TRUE);
        $keterangan = $this->input->post('KETERANGAN', TRUE);

        $jenis_kredit = $this->input->post('TIPE_KREDIT', TRUE);

        $margin = $this->input->post('MARGIN', TRUE) / $lama;
        if($margin == "")
            $margin = $bunga * $jumlah / 100;

        if ($info_usaha == 0 || $info_usaha == "")
        {
            $info_usaha = $this->input->post('KODE_USAHA', TRUE);
            $jenis_kredit = 2;
            $margin = $this->input->post('MARGIN', TRUE);
        }

        $id_anggota = $this->input->post('ID_ANGGOTA', TRUE);
        if($id_anggota == "")
            $id_anggota = $this->session->userdata("ID_ANGGOTA");

        $data = array(
                'ID_ANGGOTA' => $id_anggota,
                'ID_JENIS_KREDIT' => $jenis_kredit,
                'ID_INFO_USAHA' => $info_usaha,
                'TGL_PINJAMAN' => $this->input->post('TANGGAL_PINJAM', TRUE),
                'LAMA_PINJAMAN' => $lama,
                'JUMLAH_PINJAMAN' => $jumlah,
                'MARGIN' => $margin,
                'CICILAN_POKOK' => $jumlah / $lama,
                'BUNGA' => $bunga,
                'KETERANGAN' => $keterangan
        );
        $this->local_client->where('ID_PINJAMAN',$this->input->post('ID_PINJAMAN', TRUE));
        return $this->local_client->update('pinjaman', $data);
    }

    function generate_potongan($date) {
        $year = date("Y", strtotime($date));
        $month = date("m", strtotime($date));
        $day = 1;

        $response_message['pinjaman'] = '';
        $response_message['simpananwajib'] = '';
        $response_message['nodata'] = '';
        $kode_unit = $this->input->post('KODE_UNIT',TRUE);

        //generate table pinjaman
        $count_data = 0;
        $count_done = 0;

        /*$quer = "select a.KODE_UNIT, p.* from pinjaman p, anggota a
        where a.ID_ANGGOTA=p.ID_ANGGOTA and a.KODE_UNIT='$kode_unit'
        and p.STATUS=1 and p.PIUTANG>0";
        $query = $this->local_client->query($quer);*/

        $this->local_client->select('anggota.KODE_UNIT');
        $this->local_client->select('pinjaman.*');
        $this->local_client->join('anggota','anggota.ID_ANGGOTA = pinjaman.ID_ANGGOTA');
        $this->local_client->where('anggota.KODE_UNIT',$kode_unit);
        $this->local_client->where('pinjaman.STATUS',1);
        $this->local_client->where('pinjaman.PIUTANG > ',0);
        $query = $this->local_client->get('pinjaman');

        if ($query->num_rows() > 0) {
            $potongan = 0;
            $count_data = $query->num_rows();
            $this->local_client->trans_start();
            foreach($query->result() as $result)
            {
                if ($result->ID_JENIS_KREDIT==2) 
                {
                    if($current->MARGIN == 0)

                    $potongan = $result->CICILAN_POKOK + $result->MARGIN;
                }else if ($result->ID_JENIS_KREDIT==3) {
                    $potongan = $result->CICILAN_POKOK + (($result->BUNGA/100)*$result->PIUTANG);
                }

                if ($result->PIUTANG - $potongan < 0){
                    $potongan = $result->PIUTANG;
                }

                if($potongan < 0){
                    $potongan = 0;
                }

                $data = array(
                    'POTONGAN' => $potongan
                    );

                $this->local_client->where('ID_PINJAMAN',$result->ID_PINJAMAN);
                $this->local_client->update('pinjaman',$data);

                if ($this->local_client->affected_rows() > 0) $count_done++;
            }
            $this->local_client->trans_complete();

            if ($count_done>0) $response_message['pinjaman'] = "Data buat potongan pinjaman berhasil $count_done dari $count_data data.";
        }


        //generate table pinjaman
        $count_data = 0;
        $count_done = 0;

        $this->local_client->where('STATUS',1);
        $this->local_client->where('KODE_UNIT',$kode_unit);
        $query = $this->local_client->get('anggota');

        if ($query->num_rows() > 0) {
            $count_data = $query->num_rows();
            $this->local_client->trans_start();
            foreach($query->result() as $result) {
                $data = array(
                        'TGL_IURAN' => date('Y-m-d',mktime(0,0,0,$month,$day,$year))
                );

                $this->local_client->where('ID_ANGGOTA',$result->ID_ANGGOTA);
                $this->local_client->update('anggota',$data);

                if ($this->local_client->affected_rows() > 0) $count_done++;
            }
            $this->local_client->trans_complete();

            if ($count_done>0) $response_message['simpananwajib'] = "Data buat potongan simpanan wajib berhasil $count_done dari $count_data data.";

        }


        if ($response_message['simpananwajib']=='' && $response_message['pinjaman']=='') {
            $response_message['nodata'] = 'Tidak ada data yang diproses!';
        }

        return $response_message;

    }

    function get_potongan($date = null, $info_usaha = null) {
        $year = date("Y", strtotime($date));
        $month = date("m", strtotime($date));

        $kode_unit = $this->input->post('KODE_UNIT',TRUE);

        //generate table pinjaman
        $count_data = 0;
        $count_done = 0;
        $where = '';

        $this->local_client->select('pinjaman.*');
        $this->local_client->join('anggota','anggota.ID_ANGGOTA = pinjaman.ID_ANGGOTA');
        $this->local_client->where('anggota.KODE_UNIT',$kode_unit);
        $this->local_client->where('pinjaman.STATUS',1);
        $this->local_client->where('pinjaman.POTONGAN >',0);
        $n = 0;
        foreach($info_usaha as $cekbox)
            if ($this->input->post($cekbox->KODE_USAHA,true)>0)
            {
            if ($n==0) {
                $where .= "( pinjaman.ID_INFO_USAHA = $cekbox->KODE_USAHA";
            }else {
                $where .= " or pinjaman.ID_INFO_USAHA = $cekbox->KODE_USAHA";
            }
            $n++;
        }
        $where .= " )";
        $this->local_client->where($where);
        $query = $this->local_client->get('pinjaman');

        if ($query->num_rows() > 0) {

        }

        return $query;

    }

    function generate_realisasi($date,$info_usaha,$id_bukti){
        $year = date("Y", strtotime($date));
        $month = date("m", strtotime($date));
        $day = 1;
        $query = $this->get_potongan($date,$info_usaha);
        $count_data = $query->num_rows();

        $count_done = 0;
        $this->local_client->trans_start();
        foreach($query->result() as $result)
            {
            $bunga_rupiah = 0;
            if ($result->ID_JENIS_KREDIT == 2){
                $bunga_rupiah = $result->MARGIN;
            } else if ($result->ID_JENIS_KREDIT == 3){
                $bunga_rupiah = ($result->BUNGA / 100) * $result->PIUTANG;
            }

            $piutang = $result->PIUTANG - $result->CICILAN_POKOK + $bunga_rupiah;

            if ($result->PIUTANG-$result->POTONGAN <= 0){
                $piutang = 0;
            }

                $data = array(
                    'POTONGAN' => 0,
                    'ANGSURAN_KE' => ($result->ANGSURAN_KE + 1),
                    'PIUTANG' => $piutang
                );


            $this->local_client->where('ID_PINJAMAN',$result->ID_PINJAMAN);
            $this->local_client->update('pinjaman',$data);

            if ($this->local_client->affected_rows() > 0) $count_done ++;

            // insert into tabel realisasi
                $data = array(
                    'ID_PINJAMAN' => $result->ID_PINJAMAN,
                    'ID_ANGGOTA' => $result->ID_ANGGOTA,
                    'ID_INFO_USAHA' => $result->ID_INFO_USAHA,
                    'ID_BUKTI_TRANSAKSI' => $id_bukti,
                    'JUMLAH_POTONGAN' => $result->PIUTANG,
                    'ANGSURAN_KE' => ($result->ANGSURAN_KE+1),
                    'TGL_REALISASI' => date('Y-m-d',mktime(0,0,0,$month,$day,$year))
                );

                $this->local_client->insert('realisasi',$data);

        }
        $this->local_client->trans_complete();

        $response = "Data pinjaman berhasil direalisasikan sebanyak $count_done dari $count_data data.";
        return $response;
    }

    function sum_potongan($date = null) {
        $year = date("Y", strtotime($date));
        $month = date("m", strtotime($date));

        $kode_unit = $this->input->post('KODE_UNIT',TRUE);

        $query = "select sum(pj.POTONGAN) as total
            from pinjaman pj, anggota ag
            where ag.ID_ANGGOTA = pj.ID_ANGGOTA and ag.KODE_UNIT = '$kode_unit'
            and pj.STATUS = 1 and pj.POTONGAN > 0
            ";

        $result = $this->db->query($query);

        return $result;

    }


    function delete($id) {
        $this->local_client->where('ID_PINJAMAN', $id);
        $this->local_client->delete('pinjaman');
    }
}
?>
